package com.advertisementsystem.Mapper;

import com.advertisementsystem.entity.Statistics;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;


@Mapper
public interface StatisticsMapper extends BaseMapper<Statistics> {

    // 1. 获取广告总播放次数
    @Select("SELECT COALESCE(SUM(play_count), 0) as total_play_count " +
            "FROM statistics " +
            "WHERE ad_id = #{adId}")
    Integer getTotalPlayCount(@Param("adId") Long adId);

    // 2. 获取广告总观看次数
    @Select("SELECT COALESCE(SUM(view_count), 0) as total_view_count " +
            "FROM statistics " +
            "WHERE ad_id = #{adId}")
    Integer getTotalViewCount(@Param("adId") Long adId);

    // 3. 获取广告每日统计数据
    @Select("SELECT date, " +
            "COALESCE(SUM(play_count), 0) as play_count, " +
            "COALESCE(SUM(view_count), 0) as view_count " +
            "FROM statistics " +
            "WHERE ad_id = #{adId} " +
            "GROUP BY date " +
            "ORDER BY date")
    List<Map<String, Object>> getDailyStatistics(@Param("adId") Long adId);

    // 4. 获取广告在各设备上的统计数据
    @Select("SELECT d.name as device_name, " +
            "d.device_code, " +
            "d.location, " +
            "COALESCE(SUM(s.play_count), 0) as play_count, " +
            "COALESCE(SUM(s.view_count), 0) as view_count " +
            "FROM statistics s " +
            "JOIN device d ON s.device_id = d.id " +
            "WHERE s.ad_id = #{adId} " +
            "GROUP BY d.id, d.name, d.device_code, d.location")
    List<Map<String, Object>> getDeviceStatistics(@Param("adId") Long adId);

    // 5. 获取设备总播放次数
    @Select("SELECT COALESCE(SUM(play_count), 0) as total_play_count " +
            "FROM statistics " +
            "WHERE device_id = #{deviceId}")
    Integer getDeviceTotalPlayCount(@Param("deviceId") Long deviceId);

    // 6. 获取设备总观看次数
    @Select("SELECT COALESCE(SUM(view_count), 0) as total_view_count " +
            "FROM statistics " +
            "WHERE device_id = #{deviceId}")
    Integer getDeviceTotalViewCount(@Param("deviceId") Long deviceId);

    // 7. 获取设备每日统计数据
    @Select("SELECT date, " +
            "COALESCE(SUM(play_count), 0) as play_count, " +
            "COALESCE(SUM(view_count), 0) as view_count " +
            "FROM statistics " +
            "WHERE device_id = #{deviceId} " +
            "GROUP BY date " +
            "ORDER BY date")
    List<Map<String, Object>> getDeviceDailyStatistics(@Param("deviceId") Long deviceId);

    // 8. 获取设备播放的广告统计数据
    @Select("SELECT a.title as ad_title, " +
            "a.advertiser_id, " +
            "u.username as advertiser_name, " +
            "COALESCE(SUM(s.play_count), 0) as play_count, " +
            "COALESCE(SUM(s.view_count), 0) as view_count " +
            "FROM statistics s " +
            "JOIN advertisement a ON s.ad_id = a.id " +
            "JOIN user u ON a.advertiser_id = u.id " +
            "WHERE s.device_id = #{deviceId} " +
            "GROUP BY a.id, a.title, a.advertiser_id, u.username")
    List<Map<String, Object>> getDeviceAdStatistics(@Param("deviceId") Long deviceId);

    // 9. 获取时间范围内的广告总数
    @Select("SELECT COUNT(DISTINCT id) " +
            "FROM advertisement " +
            "WHERE created_time >= #{startDate} " +
            "AND created_time < DATE_ADD(#{endDate}, INTERVAL 1 DAY)")
    Integer getTotalAds(@Param("startDate") LocalDate startDate,
                        @Param("endDate") LocalDate endDate);

    // 10. 获取时间范围内的设备总数
    @Select("SELECT COUNT(DISTINCT id) " +
            "FROM device " +
            "WHERE created_time >= #{startDate} " +
            "AND created_time < DATE_ADD(#{endDate}, INTERVAL 1 DAY)")
    Integer getTotalDevices(@Param("startDate") LocalDate startDate,
                            @Param("endDate") LocalDate endDate);

    // 11. 获取时间范围内的总播放次数
    @Select("SELECT COALESCE(SUM(play_count), 0) " +
            "FROM statistics " +
            "WHERE date >= #{startDate} " +
            "AND date <= #{endDate}")
    Integer getOverallPlayCount(@Param("startDate") LocalDate startDate,
                                @Param("endDate") LocalDate endDate);

    // 12. 获取时间范围内的总观看次数
    @Select("SELECT COALESCE(SUM(view_count), 0) " +
            "FROM statistics " +
            "WHERE date >= #{startDate} " +
            "AND date <= #{endDate}")
    Integer getOverallViewCount(@Param("startDate") LocalDate startDate,
                                @Param("endDate") LocalDate endDate);

    // 13. 获取时间范围内的整体每日统计
    @Select("SELECT date, " +
            "COUNT(DISTINCT ad_id) as ad_count, " +
            "COUNT(DISTINCT device_id) as device_count, " +
            "COALESCE(SUM(play_count), 0) as play_count, " +
            "COALESCE(SUM(view_count), 0) as view_count " +
            "FROM statistics " +
            "WHERE date >= #{startDate} " +
            "AND date <= #{endDate} " +
            "GROUP BY date " +
            "ORDER BY date")
    List<Map<String, Object>> getOverallDailyStatistics(@Param("startDate") LocalDate startDate,
                                                        @Param("endDate") LocalDate endDate);

    // 14. 获取广告效果排名
    @Select("SELECT a.title, " +
            "u.username as advertiser_name, " +
            "COALESCE(SUM(s.play_count), 0) as total_play_count, " +
            "COALESCE(SUM(s.view_count), 0) as total_view_count, " +
            "COALESCE(SUM(s.view_count), 0) / NULLIF(COALESCE(SUM(s.play_count), 0), 0) * 100 as view_rate " +
            "FROM advertisement a " +
            "LEFT JOIN statistics s ON a.id = s.ad_id " +
            "JOIN user u ON a.advertiser_id = u.id " +
            "WHERE s.date >= #{startDate} " +
            "AND s.date <= #{endDate} " +
            "GROUP BY a.id, a.title, u.username " +
            "ORDER BY total_view_count DESC " +
            "LIMIT 10")
    List<Map<String, Object>> getTopAds(@Param("startDate") LocalDate startDate,
                                        @Param("endDate") LocalDate endDate);

    // 15. 获取设备效果排名
    @Select("SELECT d.name as device_name, " +
            "d.device_code, " +
            "d.location, " +
            "COALESCE(SUM(s.play_count), 0) as total_play_count, " +
            "COALESCE(SUM(s.view_count), 0) as total_view_count, " +
            "COALESCE(SUM(s.view_count), 0) / NULLIF(COALESCE(SUM(s.play_count), 0), 0) * 100 as view_rate " +
            "FROM device d " +
            "LEFT JOIN statistics s ON d.id = s.device_id " +
            "WHERE s.date >= #{startDate} " +
            "AND s.date <= #{endDate} " +
            "GROUP BY d.id, d.name, d.device_code, d.location " +
            "ORDER BY total_view_count DESC " +
            "LIMIT 10")
    List<Map<String, Object>> getTopDevices(@Param("startDate") LocalDate startDate,
                                            @Param("endDate") LocalDate endDate);



    /**
     * 获取最近7天的设备统计
     */
    @Select("SELECT date, SUM(play_count) as play_count, SUM(view_count) as view_count " +
            "FROM statistics WHERE device_id = #{deviceId} " +
            "AND date BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 6 DAY) AND CURRENT_DATE " +
            "GROUP BY date ORDER BY date ASC")
    List<Map<String, Object>> getDeviceWeeklyStatistics(@Param("deviceId") Long deviceId);
}